package com.achilles.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.achilles.bean.DataBean;
import com.achilles.bean.GiftBean;
import com.achilles.bean.ProjectBean;
import com.achilles.bean.UserBean;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by AchillesL on 2016-9-20.
 */
public class DBManager {

    private SQLiteDatabase mSQLiteDatabase;
    private static DBManager mDBManager = new DBManager();

    private DBManager() {
    }

    public static DBManager getInstance() {
        return mDBManager;
    }

    public void init(Context context) {
        DBHelper DBHelper = new DBHelper(context);
        mSQLiteDatabase = DBHelper.getWritableDatabase();
    }

    private void insertProjectData(ProjectBean projectBean) {
        String sql = "insert into " + DBHelper.PROJECT_TABLE_NAME + " (" +
                "projectName," +
                "projectNote," +
                "projectFixNote," +
                "projectType," +
                "projectScore," +
                "projectCreateTime," +
                "projectCompleteDate," +
                "projectCompleteSpecificTime," +
                "projectTimes," +
                "projectTotalTimes," +
                "otherInfo," +
                "projectOrder" +
                ") values (?,?,?,?,?,?,?,?,?,?,?,?);";

        Object[] objects = new Object[]{
                projectBean.getProjectName(),
                projectBean.getProjectNote(),
                projectBean.isProjectFixNote() ? 1 : 0,
                projectBean.getProjectType().ordinal(),
                projectBean.getProjectScore(),
                projectBean.getProjectCreateTime(),
                projectBean.getProjectCompleteDate(),
                projectBean.getProjectCompleteSpecificTime(),
                projectBean.getProjectTimes(),
                projectBean.getProjectTotalTimes(),
                projectBean.getOtherInfo(),
                projectBean.getProjectOrder()
        };
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void insertHistoryProjectData(ProjectBean projectBean) {
        String sql = "insert into " + DBHelper.PROJECT_HISTORY_TABLE_NAME + " (" +
                "projectName," +
                "projectNote," +
                "projectFixNote," +
                "projectType," +
                "projectScore," +
                "projectCreateTime," +
                "projectCompleteDate," +
                "projectCompleteSpecificTime," +
                "projectTimes," +
                "projectTotalTimes," +
                "otherInfo," +
                "projectOrder" +
                ") values (?,?,?,?,?,?,?,?,?,?,?,?);";

        Object[] objects = new Object[]{
                projectBean.getProjectName(),
                projectBean.getProjectNote(),
                projectBean.isProjectFixNote() ? 1 : 0,
                projectBean.getProjectType().ordinal(),
                projectBean.getProjectScore(),
                projectBean.getProjectCreateTime(),
                projectBean.getProjectCompleteDate(),
                projectBean.getProjectCompleteSpecificTime(),
                projectBean.getProjectTimes(),
                projectBean.getProjectTotalTimes(),
                projectBean.getOtherInfo(),
                projectBean.getProjectOrder()
        };
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void insertOrUpdateProjectData(ProjectBean projectBean) {
        String sql = "select * from " + DBHelper.PROJECT_TABLE_NAME + " where projectCreateTime = "
                + projectBean.getProjectCreateTime();
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        Log.d("insertOrUpdateProjectDa", "cursor.getCount():" + cursor.getCount());
        if (cursor.getCount() != 0) {
            updateProjectData(projectBean);
        } else {
            insertProjectData(projectBean);
        }
        cursor.close();
    }

    public void insertOrUpdateGiftData(GiftBean giftBean) {
        String sql = "select * from " + DBHelper.GIFT_TABLE_NAME + " where giftCreateTime = "
                + giftBean.getGiftCreateTime();
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        Log.d("insertOrUpdateGiftData", "cursor.getCount():" + cursor.getCount());
        if (cursor.getCount() != 0) {
            updateGiftData(giftBean);
        } else {
            insertGiftData(giftBean);
        }
        cursor.close();
    }

    private void insertGiftData(GiftBean giftBean) {
        String sql = "insert into " + DBHelper.GIFT_TABLE_NAME + " (" +
                "giftName," +
                "giftCreateTime," +
                "giftCompleteDate," +
                "giftCompleteSpecificTime," +
                "giftScore," +
                "giftType," +
                "otherInfo," +
                "giftOrder" +
                ") values (?,?,?,?,?,?,?,?);";
        Object[] objects = new Object[]{
                giftBean.getGiftName(),
                giftBean.getGiftCreateTime(),
                giftBean.getGiftCompleteDate(),
                giftBean.getGiftCompleteSpecificTime(),
                giftBean.getGiftScore(),
                giftBean.getGiftType().ordinal(),
                giftBean.getOtherInfo(),
                giftBean.getGiftOrder()
        };
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void insertHistoryGiftData(GiftBean giftBean) {
        String sql = "insert into " + DBHelper.GIFT_HISTORY_TABLE_NAME + " (" +
                "giftName," +
                "giftCreateTime," +
                "giftCompleteDate," +
                "giftCompleteSpecificTime," +
                "giftScore," +
                "giftType," +
                "otherInfo," +
                "giftOrder" +
                ") values (?,?,?,?,?,?,?,?);";
        Object[] objects = new Object[]{
                giftBean.getGiftName(),
                giftBean.getGiftCreateTime(),
                giftBean.getGiftCompleteDate(),
                giftBean.getGiftCompleteSpecificTime(),
                giftBean.getGiftScore(),
                giftBean.getGiftType().ordinal(),
                giftBean.getOtherInfo(),
                giftBean.getGiftOrder()
        };
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void updateProjectData(ProjectBean projectBean) {
        String sql = "update " + DBHelper.PROJECT_TABLE_NAME +
                " set projectName = ?," +
                "projectNote = ?," +
                "projectFixNote = ?," +
                "projectType = ?," +
                "projectScore = ?," +
                "projectCompleteDate = ?," +
                "projectCompleteSpecificTime = ?," +
                "projectTimes = ?," +
                "projectTotalTimes = ?," +
                "otherInfo = ?," +
                "projectOrder = ? " +
                "where " + "projectCreateTime = ?";

        String projectName = projectBean.getProjectName();
        String projectNote = projectBean.getProjectNote();
        int isProjectFixNote = projectBean.isProjectFixNote() ? 1 : 0;
        ProjectBean.ProjectType projectType = projectBean.getProjectType();
        int projectScore = projectBean.getProjectScore();
        String projectCompleteDate = projectBean.getProjectCompleteDate();
        String projectCompleteSpecificTime = projectBean.getProjectCompleteSpecificTime();
        int projectTimes = projectBean.getProjectTimes();
        int projectTotalTimes = projectBean.getProjectTotalTimes();
        String otherInfo = projectBean.getOtherInfo();
        int projectOrder = projectBean.getProjectOrder();
        String projectCreateTime = projectBean.getProjectCreateTime();

        Log.d("insertOrUpdateProjectDa",projectCreateTime);

        Object[] objects = new Object[]{
                projectName,
                projectNote,
                isProjectFixNote,
                projectType.ordinal(),
                projectScore,
                projectCompleteDate,
                projectCompleteSpecificTime,
                projectTimes,
                projectTotalTimes,
                otherInfo,
                projectOrder,
                projectCreateTime
        };
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void updateUserScore(int score) {
        String sql = "update " + DBHelper.USER_TABLE_NAME +
                " set score = ? where phoneNumber = ?";
        Object[] objects = new Object[]{score, "18620189581"};
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void updateGiftData(GiftBean giftBean) {
        String sql = "update " + DBHelper.GIFT_TABLE_NAME +
                " set giftName = ?," +
                "giftCompleteDate = ?," +
                "giftScore = ?," +
                "giftType = ?," +
                "isCompleted = ?," +
                "otherInfo = ?," +
                "giftOrder = ? " +
                "where" + " giftCreateTime = ?";

        String giftName = giftBean.getGiftName();
        String giftCompleteDate = giftBean.getGiftCompleteDate();
        int giftScore = giftBean.getGiftScore();
        int giftType = giftBean.getGiftType().ordinal();
        int isCompleted = giftBean.isCompleted() ? 1 : 0;
        int giftOrder = giftBean.getGiftOrder();
        String otherInfo = giftBean.getOtherInfo();
        String giftCreateTime = giftBean.getGiftCreateTime();

        Object[] objects = new Object[]{
                giftName,
                giftCompleteDate,
                giftScore,
                giftType,
                isCompleted,
                otherInfo,
                giftOrder,
                giftCreateTime
        };
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void deleteProjectData(ProjectBean projectBean) {
        String sql = "delete from " + DBHelper.PROJECT_TABLE_NAME + " where projectCreateTime = ?";
        Object[] objects = new Object[]{projectBean.getProjectCreateTime()};
        mSQLiteDatabase.execSQL(sql, objects);
    }

    public void deleteGiftData(GiftBean giftBean) {
        String sql = "delete from " + DBHelper.GIFT_TABLE_NAME + " where giftCreateTime = ?";
        Object[] objects = new Object[]{giftBean.getGiftCreateTime()};
        mSQLiteDatabase.execSQL(sql, objects);
    }

    private UserBean queryUserData() {
        UserBean userBean = new UserBean();
        String sql = "select * from " + DBHelper.USER_TABLE_NAME;
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        if (cursor.moveToNext()) {
            String phoneNumber = cursor.getString(cursor.getColumnIndex("phoneNumber"));
            String nick = cursor.getString(cursor.getColumnIndex("nick"));
            String password = cursor.getString(cursor.getColumnIndex("password"));
            int score = cursor.getInt(cursor.getColumnIndex("score"));
            userBean.setNick(nick);
            userBean.setPassword(password);
            userBean.setScore(score);
            userBean.setPhoneNumber(phoneNumber);
        }
        cursor.close();
        return userBean;
    }

    public int getScore() {
        UserBean userBean = queryUserData();
        return userBean.getScore();
    }

    public List<GiftBean> queryGitBeanList() {
        String sql = "select * from " + DBHelper.GIFT_TABLE_NAME + " order by giftOrder,giftScore";
        List<GiftBean> giftBeanList = new ArrayList<>();
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            String giftName = cursor.getString(cursor.getColumnIndex("giftName"));
            String giftCompleteDate = cursor.getString(cursor.getColumnIndex("giftCompleteDate"));
            String giftCreateTime = cursor.getString(cursor.getColumnIndex("giftCreateTime"));
            String giftCompleteSpecificTime = cursor.getString(cursor.getColumnIndex
                    ("giftCompleteSpecificTime"));
            int giftScore = cursor.getInt(cursor.getColumnIndex("giftScore"));
            int giftType = cursor.getInt(cursor.getColumnIndex("giftType"));
            int isCompleted = cursor.getInt(cursor.getColumnIndex("isCompleted"));
            String otherInfo = cursor.getString(cursor.getColumnIndex("otherInfo"));
            int giftOrder = cursor.getInt(cursor.getColumnIndex("giftOrder"));
            giftBeanList.add(new GiftBean(giftName, giftCreateTime, giftCompleteDate,
                    giftCompleteSpecificTime, giftScore, GiftBean.GiftType.values()[giftType],
                    isCompleted != 0, giftOrder, otherInfo));
        }
        cursor.close();
        return giftBeanList;
    }

    public List<ProjectBean> queryProjectBeanList() {
        List<ProjectBean> projectBeanList = new ArrayList<>();
        String sql = "select * from " + DBHelper.PROJECT_TABLE_NAME + " order by projectOrder";
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);

        while (cursor.moveToNext()) {
            String projectName = cursor.getString(cursor.getColumnIndex("projectName"));
            String projectNote = cursor.getString(cursor.getColumnIndex("projectNote"));
            boolean isProjectFixNote = cursor.getInt(cursor.getColumnIndex("projectFixNote")) == 1;
            ProjectBean.ProjectType projectType = ProjectBean.ProjectType.values()[cursor.getInt
                    (cursor.getColumnIndex("projectType"))];
            int projectScore = cursor.getInt(cursor.getColumnIndex("projectScore"));
            String projectCreateTime = cursor.getString(cursor.getColumnIndex("projectCreateTime"));
            String projectCompleteDate = cursor.getString(cursor.getColumnIndex
                    ("projectCompleteDate"));
            String projectCompleteSpecificTime = cursor.getString(cursor.getColumnIndex
                    ("projectCompleteSpecificTime"));
            int projectTotalTimes = cursor.getInt(cursor.getColumnIndex("projectTotalTimes"));
            int projectTimes = cursor.getInt(cursor.getColumnIndex("projectTimes"));
            int projectOrder = cursor.getInt(cursor.getColumnIndex("projectOrder"));
            String otherInfo = cursor.getString(cursor.getColumnIndex("otherInfo"));

            projectBeanList.add(new ProjectBean(projectName, projectNote, isProjectFixNote,
                    projectType, projectScore,
                    projectCreateTime, projectCompleteDate, projectCompleteSpecificTime,
                    projectOrder, projectTotalTimes,
                    projectTimes, otherInfo));
        }
        cursor.close();
        return projectBeanList;
    }

    public List<GiftBean> queryHistoryGiftBeanList(boolean isGetByDate, String completeTime) {
        List<GiftBean> giftBeanList = new ArrayList<>();
        if (isGetByDate && TextUtils.isEmpty(completeTime)) return giftBeanList;
        String sql;
        if (isGetByDate) {
            sql = "select * from " + DBHelper.GIFT_HISTORY_TABLE_NAME + " where giftCompleteDate " +
                    "= " +
                    completeTime + " order by giftOrder";
        } else {
            sql = "select * from " + DBHelper.GIFT_HISTORY_TABLE_NAME + " order by giftOrder";
        }

        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            String giftName = cursor.getString(cursor.getColumnIndex("giftName"));
            String giftCompleteDate = cursor.getString(cursor.getColumnIndex("giftCompleteDate"));
            String giftCreateTime = cursor.getString(cursor.getColumnIndex("giftCreateTime"));
            String giftCompleteSpecificTime = cursor.getString(cursor.getColumnIndex
                    ("giftCompleteSpecificTime"));
            int giftScore = cursor.getInt(cursor.getColumnIndex("giftScore"));
            int giftType = cursor.getInt(cursor.getColumnIndex("giftType"));
            int isCompleted = cursor.getInt(cursor.getColumnIndex("isCompleted"));
            String otherInfo = cursor.getString(cursor.getColumnIndex("otherInfo"));
            int giftOrder = cursor.getInt(cursor.getColumnIndex("giftOrder"));
            giftBeanList.add(new GiftBean(giftName, giftCreateTime, giftCompleteDate,
                    giftCompleteSpecificTime, giftScore, GiftBean.GiftType.values()[giftType],
                    isCompleted != 0, giftOrder, otherInfo));
        }
        cursor.close();
        return giftBeanList;
    }

    public List<ProjectBean> queryHistoryProjectBeanListByData(boolean isGetByDate, String
            completeDate) {
        List<ProjectBean> projectBeanList = new ArrayList<>();
        if (isGetByDate && TextUtils.isEmpty(completeDate)) return projectBeanList;
        String sql;
        if (isGetByDate) {
            sql = "select * from " + DBHelper.PROJECT_HISTORY_TABLE_NAME + " where " +
                    "projectCompleteDate = " + completeDate + " order by projectOrder";
        } else {
            sql = "select * from " + DBHelper.PROJECT_HISTORY_TABLE_NAME + " order by projectOrder";
        }

        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);

        while (cursor.moveToNext()) {
            String projectName = cursor.getString(cursor.getColumnIndex("projectName"));
            String projectNote = cursor.getString(cursor.getColumnIndex("projectNote"));
            boolean isProjectFixNote = cursor.getInt(cursor.getColumnIndex("projectFixNote")) == 1;
            ProjectBean.ProjectType projectType = ProjectBean.ProjectType.values()[cursor.getInt
                    (cursor.getColumnIndex("projectType"))];
            int projectScore = cursor.getInt(cursor.getColumnIndex("projectScore"));
            String projectCreateTime = cursor.getString(cursor.getColumnIndex("projectCreateTime"));
            String projectCompleteDate = cursor.getString(cursor.getColumnIndex
                    ("projectCompleteDate"));
            String projectCompleteSpecificTime = cursor.getString(cursor.getColumnIndex
                    ("projectCompleteSpecificTime"));
            int projectTotalTimes = cursor.getInt(cursor.getColumnIndex("projectTotalTimes"));
            int projectTimes = cursor.getInt(cursor.getColumnIndex("projectTimes"));
            int projectOrder = cursor.getInt(cursor.getColumnIndex("projectOrder"));
            String otherInfo = cursor.getString(cursor.getColumnIndex("otherInfo"));

            projectBeanList.add(new ProjectBean(projectName, projectNote, isProjectFixNote,
                    projectType, projectScore,
                    projectCreateTime, projectCompleteDate, projectCompleteSpecificTime,
                    projectOrder, projectTotalTimes,
                    projectTimes, otherInfo));
        }
        cursor.close();
        return projectBeanList;
    }

    public void deleteHistoryData(DataBean dataBean) {
        String sql = null;
        Object[] param = null;
        if (dataBean instanceof ProjectBean) {
            sql = "delete from " + DBHelper.PROJECT_HISTORY_TABLE_NAME +
                    " where projectCreateTime = ? and projectCompleteSpecificTime = ?";

            ProjectBean projectBean = (ProjectBean) dataBean;
            param = new Object[] {projectBean.getProjectCreateTime(),projectBean.getProjectCompleteSpecificTime()};
        }else if (dataBean instanceof GiftBean) {
            sql = "delete from " + DBHelper.GIFT_HISTORY_TABLE_NAME +
                    " where giftCreateTime = ? and giftCompleteSpecificTime = ?";

            GiftBean giftBean = (GiftBean) dataBean;
            param = new Object[] {giftBean.getGiftCreateTime(),giftBean.getGiftCompleteSpecificTime()};
        }
        mSQLiteDatabase.execSQL(sql,param);
    }

    public int getAppLoginTimes(){
        String sql = "select loginTimes from AppInfoTable";
        Cursor cursor = mSQLiteDatabase.rawQuery(sql,null);
        int loginTime = 0;
        if (cursor.moveToNext()) {
            loginTime = cursor.getInt(cursor.getColumnIndexOrThrow("loginTimes"));
        }
        cursor.close();
        return loginTime;
    }

    public void insertOrUpdateLoginTime(int loginTime) {
        String sql = "select * from " + DBHelper.APP_INFO_TABLE;
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        if (cursor.getCount() == 0) {
            insertLoginTime(loginTime);
        } else {
            updateLoginTimes(loginTime);
        }
        cursor.close();
    }

    private void insertLoginTime(int loginTimes) {
        String sql = "insert into " + DBHelper.APP_INFO_TABLE + " (loginTimes) values(?)";
        mSQLiteDatabase.execSQL(sql,new Object[]{loginTimes});
    }

    private void updateLoginTimes(int loginTimes) {
        String sql = "update " + DBHelper.APP_INFO_TABLE + " set loginTimes = " + loginTimes;
        mSQLiteDatabase.execSQL(sql);
    }

    public int getHistoryCompleteTime() {
        String sql  = "select * from " + DBHelper.PROJECT_HISTORY_TABLE_NAME;
        Cursor cursor = mSQLiteDatabase.rawQuery(sql,null);
        return cursor.getCount();
    }

}
